import requests
import json
import base64
import os
import subprocess
import time
from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey
# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.types import JSON 
from sqlalchemy import Table, Column, String
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.orm import declarative_base

# 创建数据库引擎（使用SQLite）
db_url = "sqlite:///DataBase/users.db"
engine = create_engine(db_url)

# 创建一个基本的ORM类
Base = declarative_base()

# 第一张表格：课程和视频关联
class CourseVideo(Base):
    __tablename__ = 'course_videos'

    id = Column(Integer, primary_key=True, autoincrement=True)
    course_id = Column(String, ForeignKey('courses.id'), nullable=False)
    video_id = Column(String, ForeignKey('videos.id'), nullable=False)

# 第二张表格：视频详细信息
class Video(Base):
    __tablename__ = 'videos'

    id = Column(Integer, primary_key=True,autoincrement=True)
    video_id = Column(String, unique=True, nullable=False)
    node_json = Column(JSON)
    edge_json = Column(JSON)
    tree_json = Column(JSON)

    # 建立反向关系，用于查询课程关联的视频
    courses = relationship('Course', secondary='course_videos')

# 第三张表格：课程信息
class Course(Base):
    __tablename__ = 'courses'

    id = Column(Integer, primary_key=True,autoincrement=True)
    course_id = Column(String, unique=True, nullable=False)
    repository_url = Column(String)
    # 可以添加其他课程信息字段

class User(Base):
    __tablename__ = 'users'

    student_id = Column(Integer, primary_key=True)  # 学号作为主键
    student_token = Column(String)  # 访问密钥
    email = Column(String)  # 学生邮箱

def ifStoryExist(student_id, course):
    path = f"/root/DataBase/GitStories/{student_id}/{course}"
    return os.path.exists(path)

#Linux Terminal run
def LNT(instruction):
    print(instruction)
    result = subprocess.run(instruction, shell=True, stdout=subprocess.PIPE, text=True)
    print(result.stdout)

def pushMdFileToShuishanGit(student_token,
                            course,
                            student_id,
                            md_file_name, md_file_content,
                            story_url, 
                            student_email,
                            commit_message):
    repo_dir = f"/root/DataBase/GitStories/{student_id}/{course}"

    #if not exist,clone and init
    if not ifStoryExist(student_id, course):
        
        print("story not exist, clone and init ......")

        instructions_1 = [
            f"git clone {story_url} {repo_dir}",
            f"git -C {repo_dir} remote set-url origin https://{student_token}@gitea.shuishan.net.cn/10225501403/{course}",
            f"git -C {repo_dir} branch {student_id}",
            f"git -C {repo_dir} checkout {student_id}"
        ]

        LNT(" && ".join(instructions_1))

        print("clone and init finished!")

    #write md file
    local_file_path = f"{repo_dir}/{md_file_name}"
    with open(local_file_path, "w") as f:
        f.write(md_file_content)
    f.close()
    print(f"write {md_file_name} finished!")

    #prepare to push
    with open(local_file_path, "r", encoding="utf-8") as file:
        content = file.read()
    # 将Markdown内容进行Base64编码
    content_base64 = base64.b64encode(content.encode("utf-8")).decode("utf-8")

    # 构建请求头
    headers = {
        "Authorization": f"token {student_token}",
        "Content-Type": "application/json",
    }

    # 构建请求体
    data = {
        "author": {
            "email": "10225501403@stu.ecnu.edu.cn",
            "name": "10225501403"
        },
        "committer": {
            "email": student_email,
            "name": student_id,
        },
        "content": content_base64,
        "message": commit_message,
        "branch": student_id,
    }

    # 发送POST请求
    url = f"https://gitea.shuishan.net.cn/api/v1/repos/10225501403/{course}/contents/{md_file_name}"
    response = requests.post(url, headers=headers, data=json.dumps(data))
    if response.status_code == 201:
        print(f"{student_id} successfully pushed {md_file_name} to {course} at {time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))}")
    else:
        print(f"{student_id} failed to push {md_file_name} to {course} at {time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))}")

    def getStudentInformation(student_id):
        Session=sessionmaker(bind=engine)
        session=Session()
        user=session.query(User).filter_by(student_id=student_id).first()
        token=user.student_token
        email=user.email
        session.close()
        return token,email
    def getCourseInformation(video_url):
        Session=sessionmaker(bind=engine)
        session=Session()
        video=session.query(Video).filter_by(video_id=video_url).first()
        course=video.courses[0].course_id
        course_name=video.courses[0].course_id
        repository_url=video.courses[0].repository_url
        session.close()
        return course,course_name,repository_url

if __name__ == "__main__":
    # student_token ="84eaf92cc758053e85c8edd4b02a3ee328ebc527"
    # course = "VidBot-test"
    student_id = "10225501403"
    md_file_name = "test_api_4.md"
    md_file_content = "### hello,VidBot!"
    # story_url = "https://gitea.shuishan.net.cn/10225501403/VidBot-test"
    # student_email = "10225501403@stu.ecnu.edu.cn"
    # commit_message = "test"
    student_token,student_email=getStudentInformation(student_id)
    course,course_name,story_url=getCourseInformation(video_url)
    pushMdFileToShuishanGit(student_token, course, student_id, md_file_name, md_file_content, story_url, student_email, commit_message)